﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace ShiTai_WeightService
{
    public static class DBHelper
    {
        /// <summary>
        /// 执行任意sql
        /// 有返回内容时，返回一个 object 对象 否则返回 null
        /// </summary>
        /// <param name="ef"></param>
        /// <param name="sql">sql语句</param>
        /// <param name="TimeOut">超时设置 默认30秒</param>
        /// <returns></returns>
        public static int ExecuteSql(this DbContext ef, string sql, int TimeOut = 30)
        {
            return ef.Database.ExecuteSqlRaw(sql, TimeOut);
        }

        /// <summary>
        /// 返回字典列表
        /// 因为 object 无法进行读写操作
        /// </summary>
        /// <param name="ef"></param>
        /// <param name="sql"></param>
        /// <param name="TimeOut"></param>
        /// <returns></returns>
        public static List<Dictionary<string, string?>> ExecuteSqlToDic(this DbContext ef, string sql, int TimeOut = 30)
        {
            List<Dictionary<string, string?>> dic = new List<Dictionary<string, string?>>();

            var comm = ef.Database.GetDbConnection();
            if (comm.State != ConnectionState.Open)
            {
                comm.Open();
            }
            using (var cmd = comm.CreateCommand())
            {
                cmd.CommandTimeout = TimeOut;
                cmd.CommandText = sql;
                using (var r = cmd.ExecuteReader())
                {
                    var c = r.FieldCount;
                    while (r.Read())
                    {
                        Dictionary<string, string?> values = new Dictionary<string, string?>();

                        for (int i = 0; i < c; i++)
                        {
                            var bb = r.GetName(i);
                            var a = r.GetFieldValue<object>(i);
                            //var t = r.GetFieldType(i);

                            values.Add(bb, a?.ToString());
                        }


                        dic.Add(values);

                    }
                }
            }

            return dic;
        }
    }
}
